﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CD
{
    public class dbHelpers
    {
        public String EjecutarComandoSP(MySqlCommand p_oMySqlCommand)
        {
            int intResultado = 0;
            try
            {
                using (p_oMySqlCommand)
                {
                    using (MySqlConnection oMySqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ProyectoSalvadorDelMundo.Properties.Settings.Connectionstring"].ConnectionString))
                    {
                        p_oMySqlCommand.CommandType = CommandType.StoredProcedure;
                        p_oMySqlCommand.Connection = oMySqlConnection;
                        p_oMySqlCommand.Connection.Open();
                        intResultado = p_oMySqlCommand.ExecuteNonQuery();
                        p_oMySqlCommand.Connection.Close();
                    }
                }
            }
            catch (MySqlException ex)
            {
                return ex.Message.IndexOf("Cannot delete or update a parent row: a foreign key constraint fails") > -1 ? "El registro no se puede borrar por que tiene reistros relacionados." : ex.Message;
            }
            return intResultado > 0 ? "Cambios aplicados con exito." : "Ocurrio un error al guardar los cambios.";
        }
        public String EjecutarComandoText(String p_strComando)
        {
            int intResultado = 0;
            try
            {
                using (MySqlCommand oMySqlCommand = new MySqlCommand())
                {
                    using (MySqlConnection oMySqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ProyectoSalvadorDelMundo.Properties.Settings.Connectionstring"].ConnectionString))
                    {
                        oMySqlCommand.CommandType = CommandType.Text;
                        oMySqlCommand.CommandText = p_strComando;
                        oMySqlCommand.Connection = oMySqlConnection;
                        oMySqlCommand.Connection.Open();
                        intResultado = oMySqlCommand.ExecuteNonQuery();
                        oMySqlCommand.Connection.Close();
                    }
                }
            }
            catch (MySqlException ex)
            {
                return ex.Message.IndexOf("Cannot delete or update a parent row: a foreign key constraint fails") > -1 ? "El registro no se puede borrar por que tiene reistros relacionados." : ex.Message;
            }
            return intResultado > 0 ? "Cambios aplicados con exito." : "Ocurrio un error al guardar los cambios.";
        }
        public DataTable EjecutarConsultaSP(MySqlCommand p_oMySqlCommand)
        {
            var oDataTable = new DataTable();
            try
            {
                using (DataSet oDataSet = new DataSet())
                {
                    using (MySqlDataAdapter oMySqlDataAdapter = new MySqlDataAdapter())
                    {
                        using (MySqlConnection oMySqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ProyectoSalvadorDelMundo.Properties.Settings.Connectionstring"].ConnectionString))
                        {
                            using (p_oMySqlCommand)
                            {
                                p_oMySqlCommand.CommandType = CommandType.StoredProcedure;
                                p_oMySqlCommand.Connection = oMySqlConnection;
                                oMySqlDataAdapter.SelectCommand = p_oMySqlCommand;
                                oMySqlDataAdapter.Fill(oDataSet);
                            }
                        }
                        if (oDataSet.Tables.Count > 0)
                        {
                            oDataTable = oDataSet.Tables[0];
                        }
                    }
                }
                return oDataTable;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
        }
        public DataTable EjecutarConsultaText(string p_strConsulta)
        {
            var oDataTable = new DataTable();
            try
            {
                using (DataSet oDataSet = new DataSet())
                {
                    using (MySqlDataAdapter oMySqlDataAdapter = new MySqlDataAdapter())
                    {
                        using (MySqlConnection oMySqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ProyectoSalvadorDelMundo.Properties.Settings.Connectionstring"].ConnectionString))
                        {
                            using (MySqlCommand oMySqlCommand = new MySqlCommand())
                            {
                                oMySqlCommand.CommandType = CommandType.Text;
                                oMySqlCommand.CommandText = p_strConsulta;
                                oMySqlCommand.Connection = oMySqlConnection;
                                oMySqlDataAdapter.SelectCommand = oMySqlCommand;
                                oMySqlDataAdapter.Fill(oDataSet);
                            }
                        }
                        if (oDataSet.Tables.Count > 0 )
                        {
                            oDataTable = oDataSet.Tables[0];
                        }
                    }
                }
                return oDataTable;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
        }
    }
}
